# %% Jarvis word search
# from numpy.core.defchararray import index
import re

from numpy.core.numeric import extend_all
import xlwings as xw
# import fuzzywuzzy as fz
import numpy as np
import pandas as pd
# from xlwings.main import Names
import utility as ut

kAgent = 'agent'
kSent = 'sentTo'
kApprove = 'approve'
kCon = 'content'
kWorker = 'worker'
kOwner = 'owner'
kNum = 'number'

sheet_range = None
nn = -1
_histroy_record=[]

# %% read excel

# 2:agent 3:sentTo 4:content 6:approve 7:worker owner 5:number
# add_a_record(['电信院','个人','毕业证书','科发院','刘政','周晨','3'])
def add_a_record(items: dict):
    if len(xw.apps) == 0:
        return False, '未打开Excel应用', None
    if len(xw.books) == 0: return False, "未打开工作簿", None
    if len(items) < 7: return False, "表单数量不足", None
    global sheet_range
    sheet = xw.sheets.active
    sheet_range = sheet.used_range
    # current row of edit
    nn = sheet.range("C1").end('down').row
    
    sheet_range[nn, 2:4].value = [items[kAgent], items[kSent]]  # agent sentTo
    s = items[kCon]
    if items[kOwner] != '':
        s += '（' + items[kOwner]
    sheet_range[nn, 4].value = s  # content
    sheet_range[nn, 5].value = items[kNum]  # number
    sheet_range[nn, 6].value = items[kApprove]  # approve
    sheet_range[nn, 7].value = items[kWorker]  # worker
    if items[kAgent] != '':
        return True, "done", sheet_range[nn + 1, 0].value
    return True, "done", sheet_range[nn, 0].value

def invoke_once():
    pass

def append_last_number(num: str):
    if num == '':
        return False, "空值"
    if len(xw.apps) == 0:
        return False, '未打开Excel应用'
    if len(xw.books) == 0: return False, "未打开工作簿"
    sheet = xw.sheets.active
    sheet_range = sheet.used_range
    # current row of edit
    n = sheet.range("C1").end('down').row - 1
    if num.startswith('——'):
        s = str(sheet_range[n, 4].value).replace('(', '（')
        if s == '':
            return False, "内容为空"
        if '（' in s:
            ind = s.index('（')
            s = s[:ind] + num + s[ind:]
        else:
            s = s + num
        sheet_range[n, 4].value = s
        return True, num[2:]
    if num[0] == '=':
        sheet_range[n, 5].value = num[1:]
        return True, num
    s = sheet_range[n, 5].value
    if s is None or s == '':
        ori = 0
    else:
        s = str(s)
        res = re.findall('\d+', s)
        if not res:
            ori = 0
        else:
            ori = int(res[0])
    m = 0
    if num[0] == '+':
        m = 1
        # ori += int(num)
    elif num[0] == '-':
        m = -1
        # ori -= int(num)
    # else:
    res = re.findall('\d+', num)
    if not res:
        return False, '未知操作, 用法:=数量, +数量, -数量'
    # else:
    if m == 0:
        ori = int(res[0])
    else:
        ori += int(res[0]) * m
    sheet_range[n, 5].value = str(ori)
    return True, str(ori)


# %% data pre-process

def load_data_from_excel(sheet) -> pd.DataFrame:
    column_name = 'NO., date, agent, sentTo, content, \
    number, approve, worker,guardian, remark, sealType' \
        .replace(' ', '').split(',')
    column_name.extend(['unknown' + str(i + 1)
                        for i in range(sheet.used_range.shape[1] - len(column_name))])

    data = pd.DataFrame(sheet.used_range.value, columns=column_name)
    d = data[kCon].str.rsplit('（', expand=True, n=1)
    data[kCon] = d[0]
    data[kOwner] = d[1]
    # pd.DataFrame()
    return data


# data = load_data_from_excel(xw.sheets.active)
# %% save all banks


def save_all_banks(data):
    bank_dir = 'banks/'
    # these field are name-like
    for c in 'agent sentTo approve worker content owner'.split(' '):
        data[c] = data[c].apply(
            lambda s: (s.strip() if '、' not in s else None) if type(s) is str else s)
        std_field = data[c].dropna()
        order = std_field.value_counts()  # .reset_index()  # 统计频率
        order.name = 'count'
        order.to_csv(bank_dir + 'bank_' + c + '.csv',
                     header='count', index=True)


# save_all_banks(data)
# save_all_banks(load_data_from_excel())
# %% build worker to agent map
# worker_count = ut.banks_load_for(kOwner)


# return {workerName:[[agents],[counts]]}
def collect_agents_by_worker(data: pd.DataFrame, bank_by: pd.DataFrame,
                             saveTo='banks/worker_to_agent',
                             by_key='worker') -> dict:
    worker_agent_book = {}
    for by_people in bank_by.index:
        applie_agent = data.loc[data[by_key] == by_people]['agent'].apply(
            lambda s: s.strip() if type(s) is str and '、' not in s else None) \
            .dropna().value_counts().sort_values(ascending=False)

        worker_agent_book[by_people] = [
            list(applie_agent.index), applie_agent.values.tolist()]
    np.save(saveTo, worker_agent_book)
    return worker_agent_book


# worker_agent_book = collect_agents_by_worker(data, worker_count,saveTo='banks/owner_to_agent',by_key=kOwner)
# %% TODO: build agent to work
# alias_agent_book = np.load('banks/alias_agent_book.npy', allow_pickle=True).item()
# agents = ut.banks_load_for('agents')
# agent_to_worker = {}
# bank_bypass_agent_freq=pd.DataFrame(columns=['agent','freq'])
# for agent in agents.index:
#     applie_agent = data.loc[data['agent'] == agent, 'worker'] \
#         .apply(lambda s: s.strip() if '、' not in s and len(s) < 6 else None) \
#         .dropna() \
#         .value_counts().sort_values(ascending=False)
#     agent_to_worker[agent] = [
#         list(applie_agent.index), applie_agent.values.tolist()]
#
# np.save('banks/agent_to_worker', agent_to_worker)


# read_dictionary = np.load('banks/worker_agent.npy',allow_pickle=True).item()
# bank_bypass_agent_freq.to_csv(dir+'bypass_agent_freq.csv',header=True)
# bank_bypass_agent_freq = pd.read_csv(dir+'bypass_agent_freq.csv', index_col=0)

# %%

# counts = ut.banks_load_for('agent_count')
# counts = pd.Series(counts)
# counts.to_csv('banks/agent_count.csv', header=True)

# %%

'''

#0e4bd9 221,239,217
#0479b0 190 250 176
#005a9e 205,255,158
#35455b 214 106 91

rgb(242, 242, 242)
rgb(110, 110, 110)

rgb(89, 168, 105)

rgb(219, 88, 96)
rgb(195, 214, 232)

,QTextBrowser,QTextEdit,QCommandLinkButton

QTextEdit{
    border-radius:15px;
    #background-color:#ffffff;
}

*{font: normal 20px "微软雅黑";}
QMainWindow{
    border-radius:7px;
    background-color:rgb(242, 242, 242);
    }

QWidget{
    border-radius:7px;
    background-color:rgb(242, 242, 242);
    }

'''
